declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
declare @month nvarchar(100)=(select [MAX_MONTH] from [DATE_EM_REPORT])
declare @date_end_month nvarchar(100)= (select [MAX_DATE_ENDMONTH] from [DATE_EM_REPORT])
declare @mydate date = (CONVERT(date,@date_end_month))
declare @date_begin_month nvarchar(100)= (SELECT CONVERT(VARCHAR(25),DATEADD(d,-(DAY(@mydate)-1),@mydate),112))
declare @mydate2 date = (CONVERT(date,@date_begin_month))
declare @intMonth int = month(CONVERT(date,@date_begin_month))
declare @lastDate nvarchar(6) = convert(nvarchar(6), (DATEADD(yy, -1, (DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)))),112)
declare @fromDate nvarchar(8)
declare @toDate nvarchar(8)
declare @monthCompare nvarchar(6)

--print (@intMonth)


if((SELECT MAX(YEARMONTH) FROM DPTB_MASTER_ENDMONTH_2016)<@month)
begin
insert DPTB_MASTER_ENDMONTH_2016 
SELECT ACCTNO, YEARMONTH, CIF, BAL_QD,CUS_OPEN_DATE,ISSUE_DATE,OPENNING_DATE, MATDT
FROM [SERVER74].[BICDATA].[DBO].[DPTB_MASTER_ENDMONTH] 
WHERE YEARMONTH > (SELECT MAX(YEARMONTH) FROM DPTB_MASTER_ENDMONTH_2016) AND YEARMONTH <= @month
  end

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE o.xtype IN ('U') AND o.name = 'DPTB_MASTER_ACCOUNT_PLANNING')
DROP TABLE DPTB_MASTER_ACCOUNT_PLANNING

SELECT * INTO DPTB_MASTER_ACCOUNT_PLANNING 
	FROM DPTB_MASTER_ENDMONTH_2016 WHERE CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE o.xtype IN ('U') AND o.name = 'DPTB_MASTER_ACCOUNT_PLANNING_2016')
Delete from DPTB_MASTER_ACCOUNT_PLANNING_2016

--select * from DPTB_MASTER_ACCOUNT_PLANNING


declare @count int =0
while @count < @intMonth
begin
set @fromDate = CONVERT(nvarchar(8), DATEADD(m, -@count, @mydate2), 112)
set @toDate = CONVERT(nvarchar(8), DATEADD(m, -@count, @mydate), 112)
set @monthCompare = CONVERT(nvarchar(6), DATEADD(m, -@count, @mydate), 112)

Insert DPTB_MASTER_ACCOUNT_PLANNING_2016
SELECT *  FROM DPTB_MASTER_ACCOUNT_PLANNING
	WHERE yearmonth = @monthCompare and (OPENNING_DATE BETWEEN @fromDate AND @toDate
	OR ISSUE_DATE BETWEEN @fromDate AND @toDate)
	ORDER BY YEARMONTH

	set @count = @count +1

end
	--select * from DPTB_MASTER_ACCOUNT_PLANNING_2016